﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;
using System.Threading;
using RegisterationCommon.Common;

namespace RegisterationSystem.SyncService
{
    /// <summary>
    /// ExcelHelper 的摘要说明
    /// </summary>
    public class ExcelHelper : IHttpHandler
    {
        protected string saveFilePath = "";
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string type = context.Request.QueryString["type"];
            switch (type)
            {
                case "derive_excelListAll"://全部导出
                    derive_excelListAll(context);
                    break;
                case "derive_excelList"://选定导出
                    derive_excelList(context);
                    break;
                default:
                    break;
            } 
          //  context.Response.Write("Hello World");
        }
       public void DirectoryInfoList(HttpContext context)
        {
            int day = 0;
            DirectoryInfo theFolder = new DirectoryInfo(context.Server.MapPath(@"../StudentInfoToExcel/"));
            FileInfo[] dirInfo = theFolder.GetFiles();
            //遍历文件夹
            foreach (FileInfo NextFile in dirInfo)
            {
                string filename = NextFile.Name;
                string[] Date = filename.Split('_');
                if (Date.Length > 1)
                {
                    string strTimeNow = DateTime.Now.ToString("yyyyMMdd");
                    int d1 = int.Parse(Date[1]);
                    int d2 = int.Parse(strTimeNow);
                    int ND = d2 - d1;
                    day = ND;
                    if (day >= 1)
                    {
                        File.Delete(context.Server.MapPath(@"../StudentInfoToExcel/" + filename));
                    }
                }
            }
        }
        public void derive_excelList(HttpContext context)
        {
            DirectoryInfoList(context);
            //获取修改ID
            string stuID = "";
            string idlist = context.Request.QueryString["id"];
            string[] ids = idlist.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < ids.Length; i++)
            {
                if (i != (ids.Length - 1))
                {
                    stuID = stuID + ids[i] + " or id=";
                }
                else
                {
                    stuID = stuID + ids[i];
                }
            }
            DataTable dt = DBAction.GetDTFromSQL("select *,(select Name from Preschools where ID=Students.PreschoolsID) as kindergartenName  from " + ETableName.Students + " where ID=" + stuID);
            LoadTotalReport(context, dt);
        }
        public void derive_excelListAll(HttpContext context)
        {
            DirectoryInfoList(context);
            string where = context.Request.QueryString["where"];
            string typeid = context.Request.QueryString["typeid"];
            DataTable dt = GetStudentBySchoolID(where, typeid, context);
            LoadTotalReport(context, dt);
        }

        public void LoadTotalReport(HttpContext context, DataTable dt)
        {
            string data = "0";
            string filename1 = "StudentInfo_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xls";//因为保存的不平凡，所以之精确到年和月  
            saveFilePath = context.Server.MapPath(@"../StudentInfoToExcel/" + filename1);
            //绘制表头
            DataTable table = new DataTable();
            table.Columns.Add("姓名", typeof(string));
            table.Columns.Add("性别", typeof(string));
            table.Columns.Add("证件号(身份证/护照)", typeof(string));
            table.Columns.Add("出生年月", typeof(string));
            table.Columns.Add("户口所在地", typeof(string));
            table.Columns.Add("住房社区", typeof(string));
            table.Columns.Add("住房小区", typeof(string));
            table.Columns.Add("门牌号", typeof(string));
            table.Columns.Add("房产证号", typeof(string));
            table.Columns.Add("户口迁入时间", typeof(string));
            table.Columns.Add("住房入住时间", typeof(string));
            table.Columns.Add("户籍地址", typeof(string));
            table.Columns.Add("第一监护人", typeof(string));
            table.Columns.Add("与学生关系", typeof(string));
            table.Columns.Add("工作单位", typeof(string));
            table.Columns.Add("联系电话", typeof(string));
            table.Columns.Add("第二监护人", typeof(string));
            table.Columns.Add("第二监护人与学生关系", typeof(string));
            table.Columns.Add("第二监护人工作单位", typeof(string));
            table.Columns.Add("第二监护人联系电话", typeof(string));
            table.Columns.Add("幼儿园", typeof(string));
            //行数据绑定
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string TrueName = dt.Rows[i]["TrueName"].ToString();
                    string Sex = dt.Rows[i]["Sex"].ToString();
                    string IDCardNumber = "'" + dt.Rows[i]["IDCardNumber"].ToString();
                    string Birthday = DateTime.Parse(dt.Rows[i]["Birthday"].ToString()).ToString("yyyyMMdd");
                    string HouseAreaID = dt.Rows[i]["HouseAreaID"].ToString();
                    string ScommName = dt.Rows[i]["ScommName"].ToString();
                    string XcommName = dt.Rows[i]["XcommName"].ToString();
                    string HouseNumber = dt.Rows[i]["HouseNumber"].ToString();
                    string HouseCard = dt.Rows[i]["HouseCard"].ToString();
                    string HouseIngoingTime = dt.Rows[i]["HouseIngoingTime"].ToString();
                    string HousingOccupancyTime = dt.Rows[i]["HousingOccupancyTime"].ToString();
                    string PermanentAddress = dt.Rows[i]["PermanentAddress"].ToString();
                    string FirstGuardianName = dt.Rows[i]["FirstGuardianName"].ToString();
                    string FirstRelationship = dt.Rows[i]["FirstRelationship"].ToString();
                    string FirstGuardianUnit = dt.Rows[i]["FirstGuardianUnit"].ToString();
                    string FirstGuardianPhone = dt.Rows[i]["FirstGuardianPhone"].ToString();
                    string SecGuardianName = dt.Rows[i]["SecGuardianName"].ToString();
                    string SecRelationship = dt.Rows[i]["SecRelationship"].ToString();
                    string SecGuardianUnit = dt.Rows[i]["SecGuardianUnit"].ToString();
                    string SecGuardianPhone = dt.Rows[i]["SecGuardianPhone"].ToString();
                    string kindergartenName = dt.Rows[i]["kindergartenName"].ToString();
                    if (bool.Parse(dt.Rows[i]["Sex"].ToString()) == true)
                    {
                        Sex = "女";
                    }
                    else
                    {
                        Sex = "男";
                    }

                    if (dt.Rows[i]["HouseAreaID"].ToString() == "0")
                    {
                        HouseAreaID = "武汉经济技术开发区（汉南区）";
                    }
                    else if (dt.Rows[i]["HouseAreaID"].ToString() == "1")
                    {
                        HouseAreaID = "市内其它区";
                    }
                    else if (dt.Rows[i]["HouseAreaID"].ToString() == "2")
                    {
                        HouseAreaID = "省内市外、外省外市";
                    }
                    else
                    {
                        HouseAreaID = "外籍(含港澳台)";
                    }

                    table.Rows.Add(TrueName, Sex, IDCardNumber, Birthday, HouseAreaID, ScommName, XcommName, HouseNumber, HouseCard, HouseIngoingTime, HousingOccupancyTime, PermanentAddress, FirstGuardianName, FirstRelationship, FirstGuardianUnit, FirstGuardianPhone, SecGuardianName, SecRelationship, SecGuardianUnit, SecGuardianPhone, kindergartenName);
                }
            }
            try
            {
               // List<int[]> merged = new List<int[]>();
                ExcelRender.RenderToExcel(table, saveFilePath);
                data = saveFilePath;
            }
            catch (Exception)
            { }

            context.Response.Clear();
            context.Response.Write(urlconvertor(data, context));
            context.Response.End();
        }
        #region 把物理路径转换为虚拟路径
        /// <summary>
        /// 把物理路径转换为虚拟路径
        /// </summary>
        /// <param name="physicalPath">物理路径</param>
        /// <returns></returns>
        private string urlconvertor(string physicalPath, HttpContext context)
        {
            string tmpRootDir = context.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString());//获取程序根目录
            string path = physicalPath.Replace(tmpRootDir, "../../"); //转换成相对路径
            path = path.Replace(@"\", @"/");
            return path;
        }
        #endregion
        #region 根据学校ID返回学生信息列表
        /// <summary>
        /// 根据学校ID返回学生信息列表
        /// </summary>
        /// <param name="context"></param>
        public DataTable GetStudentBySchoolID(string sqlwhere, string typeid, HttpContext context)
        {
            //页面查询条件
            string where = sqlwhere;
            where = "1=1";
            //初始查询条件
            // string whereList = "SchID =" + SchoolAccount.Current.SchID + "";
            string whereList = "SchID =" + Cookies.Get("SchID") + "";
            if (String.IsNullOrEmpty(where.ToString().Trim()))
            {
                string[] SelList = where.Split(new char[] { ',' }, StringSplitOptions.None);
                where = " 1=1 ";
                if (SelList[0] != "")
                {
                    where += " and TrueName like '%" + SelList[0] + "%'";//学生姓名
                }
                if (SelList[1] != "")
                {
                    where += " and IDCardNumber like '%" + SelList[1] + "%'";//学生身份证
                }
                if (SelList[2] != "99")
                {
                    where += " and Sex =" + SelList[2] + "";//性别
                }
                if (SelList[3] != "99")
                {
                    where += " and SchID =" + SelList[3] + "";//所属学校
                }
                if (SelList[4] != "99")
                {
                    where += " and HouseAreaID =" + SelList[4] + "";//户口所在地
                }
                if (SelList[5] != "999" && SelList[5] != "")
                {
                    where += " and ScommAreaID =" + SelList[5] + "";//住房社区
                }
                if (SelList[6] != "" && SelList[6] != "999")
                {
                    where += " and XcommAreaID like '%" + SelList[6] + "%'";//住房小区
                }
                if (SelList[7] != "99")
                {
                    where += " and Level = " + SelList[7] + "";//学生等级
                }
                if (SelList[8] != "" && SelList[9] != "")
                {
                    where += " and Birthday between '" + SelList[8] + "' and '" + SelList[9] + "'";//出生日月
                }
            }
            string typeID = typeid;
            switch (typeID)
            {
                ////待审核学生
                case "0":
                    whereList += " and Status=1 and IsMatured=1 and IsDelete=0 and " + where;
                    break;
                //初审通过学生
                case "1":
                    whereList += " and Status=2 and IsMatured=1 and IsDelete=0 and " + where;
                    break;
                //被放弃学生
                case "2":
                    whereList += " and Status=3 and IsMatured=1 and IsDelete=0 and " + where;
                    break;
                //被删除学生
                case "3":
                    whereList += " and Status>0 and IsMatured=1 and IsDelete=1 and " + where;
                    break;
                //不足龄学生信息
                case "4":
                    // whereList = " and IsMatured =1 and " + where;
                    whereList += " and IsMatured=0 and " + where;
                    break;
                default:
                    break;
            }

            DataTable totaldt = DBAction.GetDTFromSQL("select *,(select Name from Preschools where ID=Students.PreschoolsID) as kindergartenName from Students where " + whereList);
            return totaldt;
        }
        #endregion
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}